/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.uoc.tdp.pac4.only5.manager.impl;

import edu.uoc.tdp.pac4.only5.domain.Resource;
import edu.uoc.tdp.pac4.only5.domain.Space;
import edu.uoc.tdp.pac4.only5.exceptions.EespaisDatabaseException;
import edu.uoc.tdp.pac4.only5.manager.DatabaseConnectionManager;
import edu.uoc.tdp.pac4.only5.manager.SpaceManager;
import java.rmi.RemoteException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Josep
 */
public class SpaceManagerImpl extends AbstractManager implements SpaceManager {

    public SpaceManagerImpl(DatabaseConnectionManager databaseConnectionManager) throws RemoteException {
        super(databaseConnectionManager);
    }
    
    /**
     * @autor Alex
     * @param user
     * @return void
     */
    @Override
    public void addSpace(Space space) throws RemoteException {
        try {
            String insertStatement = "INSERT INTO spaces "
                    + "(spaceid,spacedescription,capacity,price_hour,price_day,"
                    + "url_map,how_to_get,postal_code,city,address,creation_date,"
                    + "creation_user,inactivation_date,inactivation_user,record_date) "
                    + "VALUES (" + space.getSpaceId()
                    + ",'" + space.getDescription() + "'," + space.getCapacity() + "," + space.getPriceHour() + "," 
                    + space.getPriceDay() + ",'" + space.getUrlMap() + "','" + space.getHowToGet() + "'," 
                    + space.getPostalCode() + ",'" + space.getCity() + "','" + space.getAddresss() + "','" 
                    + space.getCreationDate() + "','" + space.getCreationUser() + "','"+space.getInactivationDate()+"','" 
                    + space.getInactivationUser() + "','"+space.getRecordDate()+"');";
            
            StringBuilder sql = new StringBuilder();
            sql.append(insertStatement);
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            //preparedStatement.setString(1, space.getSpaceId());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(NullPointerException e) {
            Logger.getLogger(SpaceManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }

    }

    @Override
    public void modifySpace(Space space) throws RemoteException {
        try {
            String insertStatement = "UPDATE spaces set "
                    + "spacedescription='" + space.getDescription() + "',capacity=" + space.getCapacity() 
                    + ",price_hour=" + space.getPriceHour() + ",price_day=" + space.getPriceDay() 
                    + ",url_map='" + space.getUrlMap() + "',how_to_get='" + space.getHowToGet() 
                    + "',postal_code=" + space.getPostalCode() + ",city='" + space.getCity() 
                    + "',address='" + space.getAddresss() + "',creation_date='" + space.getCreationDate() 
                    + "',creation_user='" + space.getCreationUser() + "',inactivation_date='" + space.getInactivationDate()
                    + "',inactivation_user='" + space.getInactivationUser() + "',record_date='" + space.getRecordDate() 
                    + "' WHERE spaceid= ?";
            
            Integer varInt = space.getSpaceId();
            StringBuilder sql = new StringBuilder();
            sql.append(insertStatement);
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, varInt);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }  catch(NullPointerException e) {
            Logger.getLogger(SpaceManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    @Override
    public void deleteSpace(String spaceId) throws RemoteException {
        try {
            Integer varInt = Integer.parseInt(spaceId);
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM spaces WHERE spaceId = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            //preparedStatement.setString(1, userId);
            preparedStatement.setInt(1, varInt);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();

        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }  catch(NullPointerException e) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
        
       return;    }

    @Override
    public Space getSpace(String spaceId) throws RemoteException {
        Space space = null;
        try {
            Integer varInt = Integer.parseInt(spaceId);
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM spaces WHERE SPACEID = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            //preparedStatement.setString(1, spaceId);
            preparedStatement.setInt(1, varInt);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){
                space = getSpace(resultSet);
            }
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }
        
       return space;
    }

    @Override
    public List<Space> getSpaces() throws RemoteException, EespaisDatabaseException {
       List<Space> spaces = new ArrayList<Space>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select * from spaces");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                spaces.add(getSpace(resultSet));
            }
        } catch(SQLException ex){
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        } 
       return spaces;
    }

    @Override
    public List<Space> getSpacesByPostalCodes(String postalCode) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<Space> getUnBookedSpaces() throws RemoteException {
       String sql = "";
       return null;
    }

    private Space getSpace(ResultSet rs) throws RemoteException, SQLException {
        Space space = new Space();
        space.setAddresss(rs.getString("address"));
        space.setCapacity(rs.getInt("capacity"));
        space.setCity(rs.getString("city"));
        space.setCreationDate(rs.getDate("creation_date"));
        space.setCreationUser(rs.getString("creation_user"));
        space.setDescription(rs.getString("spacedescription"));
        space.setHowToGet(rs.getString("how_to_get"));
        space.setInactivationDate(rs.getDate("inactivation_date"));
        space.setInactivationUser(rs.getString("inactivation_user"));
        space.setPostalCode(rs.getInt("postal_code"));
        space.setPriceDay(rs.getDouble("price_day"));
        space.setPriceHour(rs.getDouble("price_hour"));
        space.setRecordDate(rs.getDate("record_date"));
        space.setSpaceId(rs.getInt("spaceid"));
        space.setUrlMap(rs.getString("url_map"));
        return space;
    }

    @Override
    public List<String> getLocationsOfSpaces() throws RemoteException, EespaisDatabaseException {
        List<String> locations = new ArrayList<String>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select distinct(city) from spaces");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                locations.add(resultSet.getString("city"));
            }
        } catch(SQLException ex){
           throw new EespaisDatabaseException(ex.getLocalizedMessage(), ex);
        }
       return locations;
    }

    @Override
    public List<Resource> getAllResourcesOfAllSpaces() throws RemoteException, EespaisDatabaseException {
        List<Resource> resources = new ArrayList<Resource>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select all_resources.* from spaces_resources left join all_resources on spaces_resources.spaceid = all_resources.resourceid");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                resources.add(ResourceManagerImpl.getResource(resultSet));
            }
        } catch(SQLException ex){
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        } 
       return resources;
    }
    
   
    public List<Space> getSpaces(String locality, Integer capacity, Double priceHour, Double priceDay, Object[] resourcesSelected) throws RemoteException, EespaisDatabaseException{
       List<Space> spaces = new ArrayList<Space>();
        try {
            StringBuilder sql = new StringBuilder();
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = null;
            if(capacity != null && priceHour!= null && priceDay != null){
                sql .append("select spaces.* from spaces inner join spaces_resources on spaces.spaceid = spaces_resources.spaceid")
                    .append(" where spaces.city=?")
                    .append(" and spaces.capacity >= ?")
                    .append(" and spaces.price_hour <= ?")
                    .append(" and spaces.price_day <= ?");

                if(resourcesSelected != null && resourcesSelected.length > 0){
                    sql.append(" and spaces.spaceid in (").append(buildSubQueryResources(resourcesSelected)).append(")");
                }
                
                System.out.println(sql.toString());
                
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, locality);
                preparedStatement.setInt(2, capacity);
                preparedStatement.setDouble(3, priceHour);
                preparedStatement.setDouble(4, priceDay);
            } else if(capacity != null && priceHour != null && priceDay == null){
                sql .append("select spaces.* from spaces inner join spaces_resources on spaces.spaceid = spaces_resources.spaceid")
                    .append(" where spaces.city=?")
                    .append(" and spaces.capacity >= ?")
                    .append(" and spaces.price_hour <= ?");

                if(resourcesSelected != null && resourcesSelected.length > 0){
                    sql.append(" and spaces.spaceid in (").append(buildSubQueryResources(resourcesSelected)).append(")");
                }
                System.out.println(sql.toString());
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, locality);
                preparedStatement.setInt(2, capacity);
                preparedStatement.setDouble(3, priceHour);
            } else if(capacity != null && priceHour == null && priceDay != null){
                sql .append("select spaces.* from spaces inner join spaces_resources on spaces.spaceid = spaces_resources.spaceid")
                    .append(" where spaces.city=?")
                    .append(" and spaces.capacity >= ?");
                    
                if(resourcesSelected != null && resourcesSelected.length > 0){
                    sql.append(" and spaces.spaceid in (").append(buildSubQueryResources(resourcesSelected)).append(")");
                }
                System.out.println(sql.toString());
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, locality);
                preparedStatement.setInt(2, capacity);
            } else if(capacity == null && priceHour == null && priceDay != null){
                     sql .append("select spaces.* from spaces inner join spaces_resources on spaces.spaceid = spaces_resources.spaceid")
                         .append(" where spaces.city=?")
                         .append(" and spaces.price_day <= ?");

                if(resourcesSelected != null && resourcesSelected.length > 0){
                    sql.append(" and spaces.spaceid in (").append(buildSubQueryResources(resourcesSelected)).append(")");
                }
                System.out.println(sql.toString());
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, locality);
                preparedStatement.setDouble(2, priceDay);
            } else if(capacity == null && priceHour != null && priceDay != null){
                sql .append("select spaces.* from spaces inner join spaces_resources on spaces.spaceid = spaces_resources.spaceid")
                    .append(" where spaces.city=?")
                    .append(" and spaces.price_hour <= ?")
                    .append(" and spaces.price_day <= ?");

                if(resourcesSelected != null && resourcesSelected.length > 0){
                    sql.append(" and spaces.spaceid in (").append(buildSubQueryResources(resourcesSelected)).append(")");
                }
                System.out.println(sql.toString());
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, locality);
                preparedStatement.setDouble(2, priceHour);
                preparedStatement.setDouble(3, priceDay);
            } else if(capacity == null && priceHour != null && priceDay == null){
                sql .append("select spaces.* from spaces inner join spaces_resources on spaces.spaceid = spaces_resources.spaceid")
                    .append(" where spaces.city=?")
                    .append(" and spaces.price_day <= ?");

                if(resourcesSelected != null && resourcesSelected.length > 0){
                    sql.append(" and spaces.spaceid in (").append(buildSubQueryResources(resourcesSelected)).append(")");
                }
                System.out.println(sql.toString());
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, locality);
                preparedStatement.setDouble(2, priceHour);
            }
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            
            while(resultSet.next()){                
                spaces.add(getSpace(resultSet));
            }
        } catch(SQLException ex){
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
       return spaces;
    }
    
    private String buildSubQueryResources(Object[] resourcesSelected){
        List<Resource> resources = new ArrayList<Resource>();

        for(Object res : resourcesSelected){
            Resource resource = (Resource)res;
            resources.add(resource);
        }
        StringBuilder sb = new StringBuilder();
        sb.append("select spaceid from spaces_resources where ");
        for (int i = 0; i < resources.size(); i++) {
            Resource resource = resources.get(i);
            sb.append(" resourceid=").append(resource.getResource_id());
            if(i < resources.size()-1){
                sb.append(" OR");
            }
        }
        return sb.toString();
    }
}
